import sys

sys.path.insert(0, "/workspace/works/workspace/machine-learning")

from tqdm import tqdm
import time
import requests
from pandas import DataFrame
import pandas as pd
from datetime import datetime
from gupiao_utlils import get_three_shichang
from dataset.mysql_utils import insert_stock_data, insert_stock_market, insert_stock_bankuai
import json
import re



def every_day_run(code):
    """
    获取每一只股票的当天数据
    """
    # 定义URL和参数
    url = "https://push2.eastmoney.com/api/qt/stock/get?"
    params = {
        "invt": "2",
        "fltt": "1",
        "cb": "jQuery35109324996585096881_1735717037307",
        "fields": "f58,f734,f107,f57,f43,f59,f169,f301,f60,f170,f152,f177,f111,f46,f44,f45,f47,f260,f48,f261,f279,f277,f278,f288,f19,f17,f531,f15,f13,f11,f20,f18,f16,f14,f12,f39,f37,f35,f33,f31,f40,f38,f36,f34,f32,f211,f212,f213,f214,f215,f210,f209,f208,f207,f206,f161,f49,f171,f50,f86,f84,f85,f168,f108,f116,f167,f164,f162,f163,f92,f71,f117,f292,f51,f52,f191,f192,f262,f294,f295,f748,f747",
        "secid": f"1.{code}" if code.startswith("6") else f"0.{code}",
        "ut": "fa5fd1943c7b386f172d6893dbfba10b",
        "wbp2u": "1277047353889980|0|1|0|web",
        "dect": "1",
        "_": "1735717037308"
    }

    # 发送HTTP请求
    response = requests.get(url, params=params)

    # 检查响应状态码
    if response.status_code == 200:
        # 获取响应文本
        text = response.text
        
        # 去除回调函数名
        start_index = text.find('(') + 1
        end_index = text.rfind(')')
        json_str = text[start_index:end_index]
        # 解析JSON数据
        data = eval(json_str)
        # 提取股票详细信息
        stock_details = data['data']
        # 字段映射
        field_mapping = {
            "f57": "代码",
            "f60": "昨收",
            "f45": "最低",
            "f46": "今开",
            "f44": "最高",
            "f51": "涨停",
            "f52": "跌停",
            "f43": "最新价",
            "f162": "市盈率(静态)",
            "f167": "市净率",
            "f168": "换手率",
            "f50": "量比",
            "f117": "流通股本(亿股)",
            "f116": "总股本(亿股)",
            "f47": "成交量",
            "f48": "成交额"    
        }
        
        # 整理数据到字典中
        stock_info = {"create_time": datetime.now().strftime("%Y-%m-%d")}
        for key, value in stock_details.items():
            if key in field_mapping:
                chinese_key = field_mapping[key]

                if key in ["f60", "f45", "f46", "f44", "f51", "f52", "f43", "f50", "f162", "f167"]:
                    stock_info[chinese_key] = value / 100
                elif key in ["f168", "f23", "f47"]:
                    stock_info[chinese_key] = value / 10000
                elif key in ["f117", "f116", "f48"]:
                    stock_info[chinese_key] = value / 100000000
                else:
                    stock_info[chinese_key] = value
        # 获取股票名称
        stock_name = stock_info.get("代码", "Unknown")
        # 获取当前日期
        current_date = datetime.now().strftime("%Y%m%d")
        # 构建文件名
        filename = f"./all_ticket_days/{stock_name}_{current_date}.xlsx"
        print(stock_info)
        insert_stock_data(stock_info, "stock_data")
        # 创建DataFrame
        df = pd.DataFrame([stock_info])
        # 保存为CSV文件，设置编码为UTF-8
        df.to_excel(filename, index=False)
    else:
        print(f"Failed to retrieve data. Status code: {response.status_code}")


def get_shichang(size, is_to_mysql=False):
    # API URL
    url = f'https://datacenter-web.eastmoney.com/api/data/v1/get?reportName=RPTA_RZRQ_LSHJ&columns=ALL&source=WEB&sortColumns=DIM_DATE&sortTypes=-1&pageNumber=1&pageSize={size}&filter=&callback=jQuery112309696695504811084_1735910018177&_=1735910018179'
    try:
        # 发送GET请求
        response = requests.get(url)
        response.raise_for_status()  # 检查请求是否成功

        # 获取响应内容
        data = response.text

        # 移除JSONP回调函数包裹
        # 提取JSON部分
        json_str = re.search(r'\({(.*)}\);', data).group(1)
                #    re.search(r'\(({.*})\);', data).group(1)
        # 将字符串转换为JSON对象
        json_data = json.loads("{" + json_str + "}")
        # 提取数据部分
        diff_data = json_data['result']['data']
        # 定义需要的列及其对应的字段名
        columns_mapping = {
            "DIM_DATE": "交易日期",
            "NEW": "收盘沪深300",
            "ZDF": "涨跌幅沪深300",
            "RZYE": "融资余额",
            "RZYEZB": "融资余额占比",
            "RZMRE": "融资买入额",
            "RZCHE": "融资偿还额",
            "RZJME": "融资净买入",
            "RQYE": "融券余额",
            "RQYL": "融券余量",
            "RQMCL": "融券卖出量",
            "RQCHL": "融券偿还量",
            "RQJMG": "融券净卖出",
            "RZRQYE": "融资融券余额",
            "RZRQYECZ": "融资融券余额差值"
        }

        # 提取所需字段并重命名
        extracted_data = []
        for item in tqdm(diff_data):
            extracted_item = {}
            # extracted_item = {columns_mapping[key]: value for key, value in item.items() if key in columns_mapping}
            for key, value in item.items():
                if key in columns_mapping:
                    if key in ['RZYE', "RZMRE", "RZCHE", "RZJME", "RQYE", "RQYL", "RQCHL", "RQMCL", "RQJMG", "RZRQYE", "RZRQYECZ"]:
                        value = value / 100000000
                    extracted_item[columns_mapping[key]] = value
            extracted_data.append(extracted_item)
            if is_to_mysql:
                insert_stock_market(extracted_item,  "stock_margin_data")
        # # 将数据转换为DataFrame
        df = pd.DataFrame(extracted_data)
        # # 显示前几行数据
        # df.to_excel("aa.xlsx")
        return df

    except requests.exceptions.RequestException as e:
        print(f"Error fetching data: {e}")


def get_bankuai(is_to_mysql=True):
    url = "https://datacenter-web.eastmoney.com/api/data/v1/get?callback=datatable5926005&reportName=RPTA_WEB_BKJYMXN&columns=ALL&pageNumber=1&pageNo=1&pageSize=100&sortColumns=FIN_NETBUY_AMT&sortTypes=-1&stat=1&filter=(BOARD_TYPE_CODE%3D%22005%22)&_=1736168545451"

    try:
        # 发送GET请求
        response = requests.get(url)
        response.raise_for_status()  # 检查请求是否成功

        # 获取响应内容
        data = response.text

        # 移除JSONP回调函数包裹
        # 提取JSON部分
        json_str = re.search(r'\({(.*)}\);', data).group(1)
                #    re.search(r'\(({.*})\);', data).group(1)
        # 将字符串转换为JSON对象
        json_data = json.loads("{" + json_str + "}")
        # 提取数据部分
        diff_data = json_data['result']['data']
        # 定义需要的列及其对应的字段名
        columns_mapping = {
            "TRADE_DATE": "交易日期",
            "BOARD_NAME": "行业名称",
            "FIN_BALANCE": "融资余额",
            "FIN_BALANCE_RATIO": "余额占流通市值比",
            "FIN_BUY_AMT": "融资买入额",
            "FIN_REPAY_AMT": "融资偿还额",
            "FIN_NETBUY_AMT": "融资净买入",
            "LOAN_BALANCE": "融券余额",
            "LOAN_BALANCE_VOL": "融券余量",
            "LOAN_SELL_VOL": "融券卖出量",
            "LOAN_REPAY_VOL": "融券偿还量",
            "LOAN_NETSELL_AMT": "融券净卖额",
            "MARGIN_BALANCE": "融资融券余额",
            "FIN_BALANCE_DIFF": "融资余额差",
        }

        # 提取所需字段并重命名
        create_date = ""
        extracted_data = []
        for item in tqdm(diff_data):
            extracted_item = {}
            # extracted_item = {columns_mapping[key]: value for key, value in item.items() if key in columns_mapping}
            for key, value in item.items():
                if key in columns_mapping:
                    if key not in ["FIN_BALANCE_RATIO", "BOARD_CODE", "TRADE_DATE", "BOARD_NAME"]:
                        value = value / 100000000
                    if key == "TRADE_DATE":
                        create_date = value
                    extracted_item[columns_mapping[key]] = value
            extracted_data.append(extracted_item)

        all_dict = {}
        for item in extracted_data:
            for k, v in item.items():
                all_dict["%s_%s" % (item["行业名称"], k)] = v
        all_dict["交易日期"] = create_date
        print(all_dict)
        if is_to_mysql:
            insert_stock_bankuai(all_dict,  "stock_bankuai_data")
        # # 将数据转换为DataFrame
        # df = pd.DataFrame(extracted_data)
        # # 显示前几行数据
        # df.to_excel("aa.xlsx")
        # return df

    except requests.exceptions.RequestException as e:
        print(f"Error fetching data: {e}")



if __name__ == "__main__":


    # 存取每个票当天的详数据
    all_ticket = get_three_shichang()
    for i in tqdm(all_ticket): 
        time.sleep(0.5)
        try:
            every_day_run(i['代码'])
        except Exception as e:
            print(str(e), i)


    # # 获取 市场数据
    all_items = get_shichang(size=1, is_to_mysql=True)

    # 获取板块的信息数据
    get_bankuai()

